from xpinyin import Pinyin
from config import DBO_MAP
import openpyxl

def delempty(fpath):
    wb=openpyxl.load_workbook(fpath)
    ws=wb[wb.sheetnames[0]]

    while True:
        if ws.cell(ws.max_row,1).value:
            break
        ws.delete_rows(ws.max_row)
    print(f'删除空白行后,剩余{ws.max_row}')

    while True:
        if ws.cell(1,ws.max_column).value:
            break
        ws.delete_cols(ws.max_column)
    print(f'删除空白列后,剩余{ws.max_column}')
    wb.save(fpath)
    return ws

def delempty2(fpath):
    wb=openpyxl.load_workbook(fpath)
    ws=wb[wb.sheetnames[0]]

    while True:
        if ws.cell(ws.max_row,1).value:
            break
        ws.delete_rows(ws.max_row)
    while True:
        if ws.cell(1,ws.max_column).value:
            break
        ws.delete_cols(ws.max_column)
    return ws

def getpy(mes):
    p=Pinyin()
    return p.get_pinyin(mes,splitter='',convert='capitalize')

def getpy_rw(mes):
    p=Pinyin()
    return p.get_pinyin(mes,splitter='_')


def getDictDatasByEecel(ws,py=1):
    datas=[]
    if py:
        headers=[getpy(ws.cell(1,col).value) for col in range(1,ws.max_column+1)]
    else:
        headers=[ws.cell(1,col).value for col in range(1,ws.max_column+1)]
    for row in range(2,ws.max_row+1):

        currow={}
        for col in range(1,ws.max_column+1):
            curtitle=headers[col-1]
            currow[curtitle]=ws.cell(row,col).value
        datas.append(currow)

    return datas

def getDictDatasByEecel2(fpath):
    datas=[]
    ws=delempty2(fpath)
    headers=[ws.cell(1,col).value.strip() for col in range(1,ws.max_column+1)]
    for row in range(2,ws.max_row+1):

        currow={}
        for col in range(1,ws.max_column+1):
            curtitle=headers[col-1]
            currow[curtitle]=ws.cell(row,col).value
        datas.append(currow)

    return datas

def getDictDatasByEecel3(fpath,tb_name):

    datas=[]
    ws=delempty2(fpath)
    cur_ys=DBO_MAP[tb_name]
    inverted_dict = {value: key for key, value in cur_ys.items()}
    headers=[inverted_dict[ws.cell(1,col).value] for col in range(1,ws.max_column+1) if inverted_dict.get(ws.cell(1,col).value)]

    for row in range(2,ws.max_row+1):
        currow={}
        for col in range(1,ws.max_column+1):
            curtitle=headers[col-1]
            currow[curtitle]=ws.cell(row,col).value
        datas.append(currow)

    return datas




def delduplicates(fpath):

    wb=openpyxl.load_workbook(fpath)
    ws=wb[wb.sheetnames[0]]
    print(f'原行数: {ws.max_row} 行')
    #需要忽略的列
    igncols=['采集日期']
    igninds=[]
    delempty2(fpath)
    header= [ws.cell(1,col).value for col in range(1,ws.max_column+1)]
    print(header)
    for igc in igncols:
        curj=0
        for tit in header:
            if igc in tit:
                igninds.append(curj)
            curj+=1
        
    # 创建一个字典来存储唯一的数据行
    unique_rows = {}

    # 遍历每一行数据
    j=0
    for row in ws.iter_rows(values_only=True):
        # 使用元组作为字典键，将每一行的数据作为值
        if j==0:
            j+=1
            continue
        ntt=list(row)
        for ignind in igninds:
            ntt.pop(ignind)
        unique_rows[tuple(ntt)] = row
        j+=1
    # 清空工作表的数据
    for row in ws.iter_rows():
        ws.delete_rows(2,ws.max_row)

    # 将唯一的数据行写回工作表
    for row in unique_rows.values():
        ws.append(row)

    # 保存工作簿
    wb.save(fpath)

    print(f'去重后剩余: {ws.max_row} 行')

def qiepian(fpath,size=500):

    wb=openpyxl.load_workbook(fpath)
    ws=wb[wb.sheetnames[0]]
    print(f'原行数: {ws.max_row} 行')
    import math
    
    fgcount=math.ceil((ws.max_row-1)/size)
    header= [ws.cell(1,col).value for col in range(1,ws.max_column+1)]

    jfg=1
    nwb=openpyxl.Workbook()
    nws=nwb[nwb.sheetnames[0]]
    nws.append(header)

    for jfg in range(1,fgcount+1):

        nwb=openpyxl.Workbook()
        nws=nwb[nwb.sheetnames[0]]
        nws.append(header)
        minr=(jfg-1)*size+2
        maxr=jfg*size+1
        if jfg==fgcount:
            maxr=ws.max_row
        for row in ws.iter_rows(min_row=minr,max_row=maxr,values_only=True):

            nws.append(row)
        npath=fpath.replace('.xlsx',f'_{jfg}.xlsx')
        nwb.save(npath)
        print(f'成功截取文档{npath}')

def Shuzitongyi(fpaths):
    
    for fpath in fpaths:
        print(f'正在修改{fpath}...')
        delempty(fpath)
        wb=openpyxl.load_workbook(fpath)
        ws=wb[wb.sheetnames[0]]

        max_row,max_col =ws.max_row,ws.max_column
        for col in range(1,max_col+1):

            cval=ws.cell(2,col).value
            if cval and str(cval).isdigit():

                for row in range(2,max_row+1):
                    try:
                        ws.cell(row,col).value=float(ws.cell(row,col).value)
                    except:
                        ws.cell(row,col).value=0

        # 保存修改后的工作簿
        wb.save(fpath)
        print(f'{fpath} 修改成功')
        wb.close()

def hebing(file_list,dirpth):
    nwb=openpyxl.Workbook()
    nws=nwb[nwb.sheetnames[0]]
    wb=openpyxl.load_workbook(file_list[0])
    ws=wb[wb.sheetnames[0]]
    headers=[ws.cell(1,col).value for col in range(1,ws.max_column+1)]
    nws.append(headers)
    for fname in file_list:
        datas=getDictDatasByEecel(delempty2(fname))
        for data in datas:
            nws.append(list(data.values()))
    nwb.save(f'{dirpth}/合并后.xlsx')